package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dto.Player;

public class DataBase implements Data {

	private static String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private static String DB_URL = "jdbc:sqlserver://127.0.0.1:1143;datebase=gametest";
	private static String DB_USER = "gn";
	private static String DB_PWD = "gn123";
	private static String LAOD_SQL = "SELECT TOP 5 u.username,u.point,t.gamename"
			+ " FROM user_point u"
			+ " LEFT JOIN type_inf t on u.type_id=t.id"
			+ " WHERE type_id=1" + " order by point DESC ";
	private static String SAVE_SQL = "INSERT INTO user_point (username,point,type_id) VALUES(?,?,?)";

	// �����ֻ����һ�� �����þ�̬�����
	static {
		// ������
		try {

			// ���� ע�����ݿ���������
			Class.forName(DRIVER);
			System.out.println("��ȡ���ݿ⡣����");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public List<Player> loadDate() {
		List<Player> players = new ArrayList();
		Connection conn = null;
		PreparedStatement stmt = null;
		// �����
		ResultSet rs = null;

		// // ���ݿ�����
		try {
			conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PWD);
			stmt = conn.prepareStatement(LAOD_SQL);
			rs = stmt.executeQuery();

			// ��ȡ����
			while (rs.next()) {

				players.add(new Player(rs.getString(1), rs.getInt(2)));
				// System.out.println(rs.getString(1)+rs.getString(2));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// ����ر���Դ
			try {
				if (conn != null) {
					conn.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (rs != null) {
					rs.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		// players.add(new Player("liuming", 100));
		// players.add(new Player("С��", 500));
		// players.add(new Player("liuming", 1000));
		// players.add(new Player("�°���", 5000));
		// players.add(new Player("����ķ", 8000));
		// players.add(new Player("ϵ���", 10000));

		return players;
	}

	@Override
	public void savaDate(Player player) {
		Connection conn = null;
		PreparedStatement stmt = null;

		// ���ݿ�����
		try {
			conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PWD);
			stmt = conn.prepareStatement(SAVE_SQL);
			stmt.setObject(1, player.getName());
			stmt.setObject(2, player.getPiont());
			stmt.setObject(3, 1);
			// ִ��sql���
			stmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// ����ر���Դ
			try {
				if (conn != null) {
					conn.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

	}

	// public static void main(String[] args) {
	// DataBase datebase = new DataBase();
	// // List<Player> players = datebase.loadDate();
	// // for(Player p:players){
	// // }
	// datebase.savaDate(new Player("gm", 3333));
	// System.out.println("�������سɹ�");
	//
	// System.out.println("���ӳɹ�");
	// // conn.close();
	// }
}
